package com.onlyxiahui.of.muc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jivesoftware.database.DbConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.onlyxiahui.of.muc.bean.AffiliationInfo;
import com.onlyxiahui.of.muc.bean.MemberInfo;
import com.onlyxiahui.of.muc.bean.RoomInfo;

public class RoomDAO {
	private static final String ADD_MEMBER = "INSERT INTO ofMucMember (roomID,jid,nickname) VALUES (?,?,?)";
	private static final Logger log = LoggerFactory.getLogger(RoomDAO.class);
	public static StringBuilder EXISTS_JID = new StringBuilder();
	public static StringBuilder ROOM_MEMBERS = new StringBuilder();
	static {
		EXISTS_JID.append("select ofmucroom.serviceID, ofmucroom.name, ofmucroom.roomid ,ofmucmember.nickname from ");
		EXISTS_JID.append(
				" ofmucroom join ofmucmember on ofmucroom.roomID = ofmucmember.roomID and ofmucmember.jid = ? and ofmucroom.name=?");
		EXISTS_JID.append(" union ");
		EXISTS_JID.append(" select ofmucroom.serviceID, ofmucroom.name, ofmucroom.roomid ,null from ");
		EXISTS_JID.append(
				" ofmucroom  join ofmucaffiliation on ofmucroom.roomID = ofmucaffiliation.roomID and ofmucaffiliation.jid = ? and ofmucroom.name=?");

		ROOM_MEMBERS.append("select ofmucroom.serviceID, ofmucroom.name, ofmucroom.roomid ,ofmucmember.nickname from ");
		ROOM_MEMBERS
				.append(" ofmucroom join ofmucmember on ofmucroom.roomID = ofmucmember.roomID and  ofmucroom.name=?");
		ROOM_MEMBERS.append(" union ");
		ROOM_MEMBERS.append(" select ofmucroom.serviceID, ofmucroom.name, ofmucroom.roomid ,null from ");
		ROOM_MEMBERS.append(
				" ofmucroom  join ofmucaffiliation on ofmucroom.roomID = ofmucaffiliation.roomID and ofmucroom.name=?");

	}

	/**
	 * 检测JID是否已经是群成员 或者管理员
	 * 
	 * @param jid
	 * @return
	 */
	public static boolean exists(String jid, String roomName) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(EXISTS_JID.toString());
			statement.setString(1, jid);
			statement.setString(2, roomName);
			statement.setString(3, jid);
			statement.setString(4, roomName);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				return true;
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		log.warn("MUCDAO exists:" + jid + ":" + roomName + ">false");
		return false;
	}

	/**
	 * 检测房间是否有用户
	 * 
	 * @param jid
	 * @return
	 */
	public static boolean existsMember(String roomName) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(ROOM_MEMBERS.toString());
			statement.setString(1, roomName);
			statement.setString(2, roomName);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				return true;
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		log.warn("MUCDAO existsMember:" + roomName + ">false");
		return false;
	}

	public static void saveMember(long roomId, String jid, String nickname) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(ADD_MEMBER);
			pstmt.setLong(1, roomId);
			pstmt.setString(2, jid);
			pstmt.setString(3, nickname);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	/**
	 * 修改 房间加入成员的昵称
	 * 
	 * @param roomid
	 *            房间id
	 * @param jid
	 *            JID
	 * @param nick
	 *            昵称
	 * @return
	 */
	public static Boolean updateNick(Long roomid, String jid, String nick) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("update ofmucmember set nickname = ? where roomID = ? and jid=?");
			pstmt.setString(1, nick);
			pstmt.setLong(2, roomid);
			pstmt.setString(3, jid);
			// 执行SQL
			return pstmt.executeUpdate() >= 0;
		} catch (Exception e) {
			e.printStackTrace();
			log.error("update room nick error：" + e.getMessage());
		} finally {
			DbConnectionManager.closeConnection(null, pstmt, con);
		}
		return false;
	}

	public static List<Map<String, String>> getMUCInfo(String jid) {

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		StringBuilder sql = new StringBuilder();
		sql.append("	select");
		sql.append("		ofmucroom.serviceID,");
		sql.append("		ofmucroom.name,");
		sql.append("		ofmucroom.roomid,");
		sql.append("		ofmucroom.roomPassword,");
		sql.append("		ofmucmember.nickname");
		sql.append("	from");
		sql.append("		ofmucroom");
		sql.append("	join ofmucmember on ofmucroom.roomID = ofmucmember.roomID");
		sql.append("	and ofmucmember.jid = ?");
		sql.append("	union");
		sql.append("	select");
		sql.append("		ofmucroom.serviceID,");
		sql.append("		ofmucroom.name,");
		sql.append("		ofmucroom.roomid,");
		sql.append("		ofmucroom.roomPassword,");
		sql.append("		'' ");
		sql.append("	from");
		sql.append("		ofmucroom");
		sql.append("	join ofmucaffiliation on ofmucroom.roomID = ofmucaffiliation.roomID");
		sql.append("	and ofmucaffiliation.jid = ?");
		
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Map<String, String> map = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, jid);
			statement.setString(2, jid);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				map = new HashMap<String, String>();
				map.put("serviceId", resultSet.getString(1));
				map.put("name", resultSet.getString(2));
				map.put("roomId", resultSet.getString(3));
				map.put("roomPassword", resultSet.getString(4));
				map.put("nickname", resultSet.getString(5));
				list.add(map);
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}

	public static List<RoomInfo> getRoomInfoList(String jid) {

		List<RoomInfo> list = new ArrayList<RoomInfo>();
		StringBuilder sql = new StringBuilder();

		sql.append("	select");
		sql.append("		ofmucroom.serviceID,");
		sql.append("		ofmucroom.name,");
		sql.append("		ofmucroom.roomid,");
		sql.append("		ofmucroom.roomPassword");
		sql.append("	from");
		sql.append("		ofmucroom");
		sql.append("	join ofmucmember on ofmucroom.roomID = ofmucmember.roomID");
		sql.append("	and ofmucmember.jid = ?");
		sql.append("	union");
		sql.append("	select");
		sql.append("		ofmucroom.serviceID,");
		sql.append("		ofmucroom.name,");
		sql.append("		ofmucroom.roomid,");
		sql.append("		ofmucroom.roomPassword");
		sql.append("	from");
		sql.append("		ofmucroom");
		sql.append("	join ofmucaffiliation on ofmucroom.roomID = ofmucaffiliation.roomID");
		sql.append("	and ofmucaffiliation.jid = ?");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		RoomInfo ri = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, jid);
			statement.setString(2, jid);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				ri = new RoomInfo();
				ri.setServiceId(resultSet.getLong(1));
				ri.setName(resultSet.getString(2));
				ri.setRoomId(resultSet.getLong(3));
				ri.setRoomPassword(resultSet.getString(4));
				list.add(ri);
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}

	public static List<AffiliationInfo> getAffiliationList(String roomName) {

		List<AffiliationInfo> list = new ArrayList<AffiliationInfo>();
		StringBuilder sql = new StringBuilder();
		sql.append("	select");
		sql.append("		ofma.jid,");
		sql.append("		ofma.affiliation");
		sql.append("	from");
		sql.append("		ofmucaffiliation ofma");
		sql.append("	left join ofmucroom ofmm on ofmm.roomID = ofma.roomID");
		sql.append("	where");
		sql.append("		1 = 1");
		sql.append("	and ofmm.`name` = ?");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		AffiliationInfo ai = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, roomName);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				ai = new AffiliationInfo();
				ai.setJid(resultSet.getString(1));
				ai.setAffiliation(resultSet.getInt(2));
				list.add(ai);
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}

	public static List<MemberInfo> getMemberInfoList(String roomName) {

		List<MemberInfo> list = new ArrayList<MemberInfo>();
		StringBuilder sql = new StringBuilder();

		sql.append("	select");
		sql.append("		ofmm.jid,");
		sql.append("		ofmm.nickname");
		sql.append("	from");
		sql.append("		ofmucmember ofmm");
		sql.append("	left join ofmucroom ofmr on ofmr.roomID = ofmm.roomID");
		sql.append("	where");
		sql.append("		1 = 1");
		sql.append("	and ofmr.`name` = ?");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		MemberInfo mi = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, roomName);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				mi = new MemberInfo();
				mi.setJid(resultSet.getString(1));
				mi.setNickname(resultSet.getString(2));
				list.add(mi);
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}

	public static boolean deleteUser(String roomName, String jid) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(
					"delete from ofmucmember where jid= ? and roomID=(select ofmr.roomID from ofmucroom ofmr where ofmr.`name`= ? )");
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(1, jid);
			pstmt.setString(2, roomName);
			return pstmt.executeUpdate() >= 0;// 执行SQL
		} catch (Exception e) {
			e.printStackTrace();
			log.error("update room nick error：" + e.getMessage());
		} finally {
			DbConnectionManager.closeConnection(null, pstmt, con);
		}
		return false;
	}

	public static String getNickname(long roomId, String jid) {
		String nickname = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select ofmm.nickname from ofmucmember ofmm where ofmm.roomID= ? and ofmm.jid= ? limit 0,1");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, roomId);
			statement.setString(2, jid);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				nickname = (resultSet.getString(1));
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return nickname;
	}
	
	
	public static RoomInfo getRoomInfo(String roomName) {

		RoomInfo info=null;
		StringBuilder sql = new StringBuilder();

		sql.append("	select");
		sql.append("		ofmucroom.serviceID,");
		sql.append("		ofmucroom.name,");
		sql.append("		ofmucroom.roomid,");
		sql.append("		ofmucroom.roomPassword");
		sql.append("	from");
		sql.append("		ofmucroom ");
		sql.append("	where ofmucroom.`name` = ?");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, roomName);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				info = new RoomInfo();
				info.setServiceId(resultSet.getLong(1));
				info.setName(resultSet.getString(2));
				info.setRoomId(resultSet.getLong(3));
				info.setRoomPassword(resultSet.getString(4));
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return info;
	}
}
